package com.mynature.booking.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.mynature.booking.client.util.SQLUtil;
import com.mynature.booking.model.Person;
import com.mynature.booking.model.TourLinkPerson;
import com.mynature.booking.model.TourInfo;


public class DatabaseManager {

	private static DatabaseManager sInstance;

	/**
	 * 
	 */
	private DatabaseManager() {
		super();
	}

	public static DatabaseManager getInstance() {
		if (null == sInstance) {
			sInstance = new DatabaseManager();
		}// end if
		return sInstance;
	}

	public static Connection mConnecct = null;

	public boolean connectMyNature() throws SQLException {

		boolean result = true;

		if (null == mConnecct) {
			try {
				Class.forName(SQLUtil.DRIVER);
				mConnecct = DriverManager.getConnection(SQLUtil.HOST_URL,
						SQLUtil.USERNAME, SQLUtil.PASSWORD);
				if (mConnecct != null) {
					System.out.println("Person connects Successful!");
					Statement s = mConnecct.createStatement();
					ResultSet rs = s.executeQuery("select * from person");

					while (rs != null && rs.next()) {
						System.out.println("person name = "
								+ rs.getString("name"));
					}
				}
			} catch (ClassNotFoundException e) {
				System.out.println("Can not connect to the database!");
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if (mConnecct != null) {
				System.out.println("Tour connects Successful");
				Statement s = mConnecct.createStatement();
				ResultSet rs = s.executeQuery("select * from tourinfo");
				while (rs != null && rs.next()) {
					System.out.println("tour place = " + rs.getString("place"));
				}

			}
		}
		return result;
	}

	/**
	 * 
	 * @return null if not exist a person, who named something
	 */
	public Person queryPersonByPersonalNumber(int idcard) {
		Person result = null;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			ResultSet rs = statement
					.executeQuery("select * from person where idcard = "
							+ idcard);
			if (null != rs&&rs.next()) {

				result = new Person();
				result.setIdcard(rs.getInt(Person.COLUMN_IDCARD));
				result.setName(rs.getString(Person.COLUMN_NAME));
				result.setSex(rs.getString(Person.COLUMN_SEX));
				result.setAge(rs.getInt(Person.COLUMN_AGE));
				result.setId(rs.getInt(Person.COLUMN_ID));
				result.setPhone(rs.getInt(Person.COLUMN_PHONE));
			}// end if
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return result;
	}

	public Person queryPersonById(int id) {
		Person person = null;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			ResultSet rs = statement
					.executeQuery("select * from person where Id = " + id);
			if (null != rs&&rs.next()) {
				person = new Person();
				person.setId(rs.getInt(Person.COLUMN_ID));
				person.setName(rs.getString(Person.COLUMN_NAME));
				person.setAge(rs.getInt(Person.COLUMN_AGE));
				person.setPhone(rs.getInt(Person.COLUMN_PHONE));
				person.setSex(rs.getString(Person.COLUMN_SEX));
			}// end if
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return person;
	}

	public boolean deletePerson(int idcard) {
		boolean result = true;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			result = statement.execute("delete from " + Person.TABLE_NAME + " where "
					+ Person.COLUMN_IDCARD + " = " + idcard);
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;

	}

	/**
	 * 
	 * @param person
	 * @return 0 means failed. return 1 mean success.
	 */
	public int insertPerson(Person person) {
		int result = 0;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			result = statement.executeUpdate("insert into Person (name,sex,age,phone,idcard)values( '"
					+ person.getName() + "', '"
					+ person.getSex() + "', " + person.getAge() + ", "
					+ person.getPhone() + ", " + person.getIdcard() + ")");
		} catch (SQLException e) {
			// TODO Error, plz enter all information from column in blank
			e.printStackTrace();
		}
		return result;
	}

	public Person updatePersonByPersonalNumber(Person person) {

		Person result = null;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			int rs = statement.executeUpdate("update Person set name = '"
					+ person.getName() + "', sex ='" + person.getSex()
					+ "', age = " + person.getAge() + ", phone = '"
					+ person.getPhone() + "' where idcard ="
					+ person.getIdcard() + "");

			if (0 != rs) {
				result = new Person();
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public ArrayList<Person> query() throws SQLException {
		ArrayList<Person> result = null;

		Statement statement = null;
		try {
			statement = mConnecct.createStatement();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		ResultSet resultSet = statement.executeQuery("select * from person");

		if (null != resultSet) {
			result = new ArrayList<Person>();
		}// end if

		while (null != resultSet && resultSet.next()) {
			Person temp = new Person();
			temp.setAge(resultSet.getInt("Age"));
			temp.setName(resultSet.getString("Name"));
			temp.setId(resultSet.getInt("id"));
			temp.setSex(resultSet.getString("Sex"));
			temp.setPhone(resultSet.getInt("Phone"));
			temp.setIdcard(resultSet.getInt("idcard"));
			result.add(temp);
		}
		return result;
	}

	public TourInfo insertTourInfo(TourInfo place1) {

		Statement statement;
		try {

			statement = mConnecct.createStatement();
			int success = statement.executeUpdate("insert into tourinfo values( "
					+  ", '" + place1.getPlace() + "', "
					+ place1.getDuration() + ", '" + place1.getStart() + "', "
					+ place1.getMaxNum() + " ," + place1.getPrice() + ", "
					+ place1.getCurNum() + ")");
			if (0 != success) {
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return null;
	}

	public boolean deleteTourInfoByID(int id) {
		boolean result = true;

		Statement statement;
		try {
			statement = mConnecct.createStatement();
			result = statement.execute("delete from Tourinfo where id = " + id);
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public TourInfo updateTourByAdmin(TourInfo place1) {

		TourInfo result = null;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			int rs = statement.executeUpdate("update Tourinfo set place = '"
					+ place1.getPlace() + "', duration = " + place1.getDuration()
					+ ", start = '" + place1.getStart()
					+ "', maxNum =" + place1.getMaxNum() + " ,"
					+ place1.getPrice() + ", " + place1.getCurNum()
					+ " where id=" + place1.getId());

			if (0 != rs) {
				result = new TourInfo();
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public TourInfo queryTourInfoById(int tourId) {

		Statement statement;
		TourInfo result = null;
		try {
			statement = mConnecct.createStatement();
			ResultSet rs = statement
					.executeQuery("select * from tourinfo where id = " + tourId);
			if (null != rs) {
				result = new TourInfo();
				result.setId(rs.getInt(tourId));
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public ArrayList<TourInfo> queryAlltTourInfo() throws SQLException {
		ArrayList<TourInfo> result = null;

		Statement statement = null;
		try {
			statement = mConnecct.createStatement();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		ResultSet resultSet = statement.executeQuery("select * from tourinfo");

		if (null != resultSet) {
			result = new ArrayList<TourInfo>();
		}// end if

		while (null != resultSet && resultSet.next()) {
			TourInfo temp = new TourInfo();
			temp.setStart(resultSet.getInt("Start"));
			temp.setCurNum(resultSet.getInt("CurNum"));
			temp.setId(resultSet.getInt("id"));
			temp.setMaxNum(resultSet.getInt("MaxNum"));
			temp.setPlace(resultSet.getString("Place"));
			temp.setPrice(resultSet.getInt("Price"));
			temp.setDuration(resultSet.getInt("Duration"));
			result.add(temp);
		}
		return result;
	}

	public TourLinkPerson queryPersonLinkTour(String id) {

		TourLinkPerson result = null;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			ResultSet rs = statement
					.executeQuery("select* from TourLinkPerson where id = "
							+ id);
			if (null != rs) {
				result = new TourLinkPerson();
				result.setId(rs.getInt(id));
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public TourLinkPerson updatePersonLinkTour(TourLinkPerson link) {

		TourLinkPerson result = null;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			int rs = statement.executeUpdate("update person_link_tour set personId = "
					+ link.getPersonId() + ", tourId= " + link.getTourId()
					+ "where id=" + link.getId());

			if (0 != rs) {
				result = new TourLinkPerson();
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public boolean deletePersonLinkTour(int id_1) {
		boolean result = true;
		Statement statement;
		try {
			statement = mConnecct.createStatement();
			result = statement.execute("delete  from tour_link_person where id = "
					+ id_1);
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;
	}

	public TourLinkPerson insertPersonLinkTourInfo(TourLinkPerson link) {

		Statement statement;
		try {

			statement = mConnecct.createStatement();
			int success = statement
					.executeUpdate("insert into tour_link_person values( "
							+ link.getId() + ", " + link.getPersonId()
							+ ", " + link.getTourId() + ")");
			if (0 != success) {
			}// end if
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return null;
	}

	public static void main(String args[]) throws SQLException {
		DatabaseManager db = new DatabaseManager();
		db.connectMyNature();
		Person p1 = new Person();
		p1.setName("Leo");
		p1.setSex("Male");
		p1.setAge(22);
		p1.setPhone(123456);
		p1.setIdcard(4733);
		db.insertPerson(p1);
		TourInfo place1 = new TourInfo();
		place1.setPlace("Sarek");
		place1.setDuration(8);
		place1.setMaxNum(12);
		place1.setStart(12);
		place1.setPrice(4000);
		place1.setCurNum(10);
		db.deleteTourInfoByID(4733);
		@SuppressWarnings("unused")
		TourLinkPerson link = new TourLinkPerson();

	}
}
